import os
import openpyxl

from common.log.log import log
from common.excel.excel_case import read_data

class Excel:
    """
    excel处理模块
    """

    def read_excel_sheets(self, file):
        """
        读取指定Excel文件的sheet名
        :param file:
        :return:
        """
        try:
            if os.path.exists(file):
                myExcel = openpyxl.load_workbook(file)
                sheets = myExcel.sheetnames
                log.info(f'读取表格的sheets成功{sheets}')
                return sheets
            else:
                log.info(f'该文件不存在{file}')
                return False
        except Exception as e:
            log.info(e)
            log.info(f"读取指定Excel文件的sheet名发生异常为{e}")
            return False

    def read_excel_cell_contents(self, file, sheet, row, column):
        """
        读取单元格内容
        :param file: 表格文件
        :param sheet: sheet页签
        :param row: 行号
        :param column: 列号
        :return:
        """
        try:
            if os.path.exists(file):
                myExcel = openpyxl.load_workbook(file)
                mySheet = myExcel.get_sheet_by_name(sheet)
                value_str = (mySheet.cell(row=row, column=column)).value
                log.info(f'值为：{value_str}, 所在行：{row}')
                return value_str
            else:
                log.info(f'该文件不存在{file}')
                return False
        except Exception as e:
            log.info(e)
            log.info(f"读取单元格内容发生异常为{e}")
            return False

    def read_excel_cell_rows(self, file, sheet, row):
        """
        读取单元格内容（按行获取）
        :param file: 表格文件
        :param sheet: sheet页签
        :param row: 行号
        :return: 返回该行所有参数，以列表的形式，如：['22644', '刘玲芳', '130105198209060665', '20180417', '20380417', '农业银行', '6228480638784865872', '河北省', None, '18713170363', '线上分销']
        """
        try:
            if os.path.exists(file):
                myExcel = openpyxl.load_workbook(file)
                mySheet = myExcel.get_sheet_by_name(sheet)
                # value_str = (mySheet.rows(row)).value

                result = []
                for cell in list(mySheet.rows)[row]:
                    result.append(cell.value)
                log.info(f'所在行：{row}')
                return result
            else:
                log.info(f'该文件不存在{file}')
                return False
        except Exception as e:
            log.info(e)
            log.info(f"读取单元格内容发生异常为{e}")
            return False


def read_all_caseexcel(file):
    # filename_path = os.path.dirname(os.path.realpath(__file__))
    # file = filename_path + '/test_api_yunke_applets_basic_query.xlsx'
    cases = read_data(file, 'api')
    # print(cases)
    # log.info(cases)
    data = []
    case_parametrize = []
    ids = []
    for case in cases:
        case_parametrize.append(case.get('method'))
        case_parametrize.append(case.get('path'))
        case_parametrize.append(case.get('data'))
        case_parametrize.append(case.get('check'))
        case_parametrize.append(case.get('case_name'))
        data.append(case_parametrize)
        case_parametrize = []  #加这一行是为了让每个数据在一个列表里面
        ids.append(case.get('case_name'))
    # log.info(data)
    # log.info(ids)
    return data,ids


if __name__ == '__main__':
    # Excel().read_excel_sheets("D/XXX/X//X")
    # file_path=''
    data=read_all_caseexcel(r'/Users/liaogan/huawang-api20230914/TestCase/test_huawang_yk/test_ht_ui/test_menu_page/test_huawang_yk_ht_menu_page.xlsx')
    for one in data:
        print(one)
    # print(data)
    # for one in data:
    #     print(one)
